CREATE DATABASE name_of_database;

Example:

CREATE DATABASE student;

student database will be created.

Create Table

To create table CREATE TABLE statement is used. Below is the syntax for creating table:

Syntax

CREATE TABLE name_of_table
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

 Example:

 
CREATE TABLE student
( 
studentID int,
First Name varchar(55),
Last Name varchar(55),
Address varchar(55),
City varchar(55)
);

Student_info table will be created.

INSERT INTO

To insert values in the table INSERT INTO statement is used. This statement is used in two ways:

Syntax:

1) INSERT INTO name_of_table VALUES (value1, value2, value3,...);

 2) INSERT INTO name_of_table (column1, column2, column3,...)

VALUES (value1, value2, value3,...); 

Example:

1) INSERT INTO student_info VALUES (1234,'John’,’Smith’,52,’London’);

  2) INSERT INTO student_info (studentid, Firstname, Lastname, City)VALUES (1234,'John’,’Smith’,’London’);

UPDATE

To update record UPDATE statement is used. Below is the syntax for updating record:

Syntax:

UPDATE name_of_table

SET column1=value1,column2=value2,...

WHERE column=value;

 NOTE: WHERE clause is used to specify which record is to be updated.

Example:

UPDATE student_info

SET FirstName='Jennifer’ WHERE FirstName='John’;

 DELETE

To delete record DELETE statement is used. Below is the syntax for deleting record:

Syntax:

DELETE FROM name_of_table

WHERE column=value;

NOTE: WHERE clause is used to specify which record is to be deleted.

Example:

DELETE FROM student_info

WHERE firstName='Jennifer' ;

 

Output: Jennifer record will be deleted.

SELECT

To select data from database SELECT statement is used. The result which we get will be stored in result set. Following is the syntax of SELECT statement:

Syntax:

SELECT column_name1, column_name2

FROM name_of_table;

 Above syntax is given where particular data is to be selected.  

SELECT * FROM name_of_table;

 Above syntax is used to fetch whole record in database.

Example:

StudentId Name Address Contact   Marks 12ST09 John USA 456788        54 12ST10 Royce Argentina 457912        34 12ST11 Mickel London 56276          45 12ST12 Tom New york 2898938      43 12ST13 Hugh Luanda 2974789      34 12ST14 John Paris 232688        56 12ST15 Jennifer USA 43762762    67

 

SELECT Name, address from student_info;

 Output:

Name Address John USA Royce Argentina Mickel London Tom New york Hugh Luanda John Paris Jennifer USA

 

SELECT * FROM student_info;

Output:

StudentId Name Address Contact   Marks 12ST09 John USA 456788        54 12ST10 Royce Argentina 457912        34 12ST11 Mickel London 56276          45 12ST12 Tom New york 2898938      43 12ST13 Hugh Luanda 2974789      34 12ST14 John Paris 232688        56 12ST15 Jennifer USA 43762762    67

 

DISTINCT

In a table, a field may have duplicate values; and you only want distinct values. So, DISTINCT keyword is used which will return only different (distinct) values. Following is the syntax of DISTINCT statement:

Syntax:

SELECT DISTINCT column_name1, column_name2

FROM name_of_table;

 Example:

SELECT DISTINCT address  FROM student_info;

 output

USA Argentina London New york Luanda Paris

WHERE

 WHERE clause filter only those data that fulfill a specified criterion. Following is the syntax of DISTINCT statement:

Syntax:

SELECT column_name1,column_name2

FROM name_of_table

WHERE column_name operator value;

 Example:

SELECT * FROM student_info

WHERE address= ‘USA’;

 Output:

12ST09        John                  USA   456788           54 12ST15 Jennifer             USA   43762762    67

 AND & OR

Like WHERE clause AND & OR operator is also used for filter.

The AND operator is used when first condition and second condition both are true.

The OR operator is used when either the first condition or second condition is true.

Example:

SELECT * FROM student_info

WHERE address='Paris'

AND marks='56';

 Output:

12ST14  John  Paris    232688        56

 

Example:

SELECT * FROM student_info

WHERE name='John'

OR address='Argentina';

 Output:

12ST09     John USA 456788     54 12ST14     John         Paris    232688                                 12ST10    Royce     Argentina 457912        34

 ORDER BY

ORDER BY is used to sort the column. By default the records is sorted in ascending order. For descending order you can use DESC keyword.

Syntax:

SELECT column_name1, column_name

FROM name_of_table

ORDER BY column_name ;

 Example

SELECT * FROM student_info

ORDER BY name;

Output:

Hugh Jennifer John John Mickel Royce Tom

 

SELECT TOP

The SELECT TOP is used to return number of data. The SELECT TOP can be very useful for thousands of record. Following is the syntax of SELECT TOP:

Syntax:

SELECT column_name

FROM name_of_table

LIMIT number;

 

Example:

SELECT *

FROM student_info

LIMIT 2;

 Output:

12ST14     John         Paris                        232688       45                          12ST10    Royce Argentina 457912        34

 LIKE

The LIKE is used with WHERE clause to search for a specific pattern. Following is the syntax:

SELECT column_name1

FROM name_of_table

WHERE column_name LIKE pattern;

 Example:

SELECT * FROM student_info

WHERE address LIKE  'a%';

 Output:

12ST10   Royce    Argentina   457912        34 12ST13  Hugh Luanda   2974789      34

Wildcards

Wildcard are used with LIKE operator. These wildcard are used to search data within a table. Following is the example of wildcard:

Example:

SELECT * FROM student_info

WHERE name LIKE  'e%';

Output:

12ST10   Royce    Argentina   457912        34

 

IN

The IN operator enable you to specify multiple values. Following is the example of wildcard:

Syntax

SELECT column_name

FROM name_of_table


WHERE column_name IN (value1,value2,...);

 Example:

SELECT * FROM student_info

WHERE address IN ('Paris','London');

 Output

12ST14     John         Paris                    232688       45 12ST11 Mickel     London                 56276          45

 

NOTE: BETWEEN, Aliases, Joins, Inner Join, Left Join, Right Join, Full Join, Union, Select Into, Insert Into Select, etc  queries are discussed in next blog.